package nl.hr.minor.mobilelife.j1213.jlr.findit;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Hostclass for the database. If the user completes all FindIT levels, he can submit his name
 * and the score will be saved into this database.
 * @author Leon
 *
 */
public class Database{

	/**
	 * Table columns
	 */
	public static final String KEY_ROWID = "_id";
	public static final String KEY_NICKNAME = "nickname";
	public static final String KEY_SCORE = "score";

	/**
	 * Table metadata
	 */
	private static final String DATABASE_NAME = "MiniGames";
	private static final String DATABASE_TABLE = "highScoreTable";

	/**
	 * Database version
	 */
	private static final int DATABASE_VERSION = 2;

	private DbHelper dbHelper;
	private final Context context;
	private SQLiteDatabase database;

	public Database(Context context){
		this.context = context;
	}

	/**
	 * This SQLiteOpenHelper is a helper class, provided by the framework, to manage
	 * the creation and versioning.
	 * @author Leon
	 *
	 */
	private static class DbHelper extends SQLiteOpenHelper{

		public DbHelper(Context context) {
			super(context, DATABASE_NAME , null, DATABASE_VERSION);
		}

		/**
		 * Called when the database is created for the first time. 
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(	"CREATE TABLE " + DATABASE_TABLE + "(" +
					KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
					KEY_NICKNAME + " TEXT NOT NULL," +
					KEY_SCORE + " INTEGER NOT NULL);"
					);

		}

		/**
		 * Called when the database needs to be upgraded.
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXIST "+DATABASE_TABLE);
			onCreate(db);
		}

	}

	/**
	 * Get a writable Database object to insert/update/read/delete scores
	 * @return The writable Database object (or readable if a writable object could not be created) 
	 * @throws SQLException
	 */
	public Database open() throws SQLException{
		dbHelper = new DbHelper(context);
		database = dbHelper.getWritableDatabase();
		return this;
	}

	/**
	 * Closes the Database connection and SQLiteOpenHelper 
	 */
	public void close(){
		dbHelper.close();
	}

	/**
	 * Insert a new score.
	 * @param nicknameText The nickname the user entered
	 * @param totalScore The amount of points gathered
	 * @return The id of the inserted row
	 */
	public long insert(String nicknameText, int totalScore) {
		ContentValues cv = new ContentValues();
		cv.put(KEY_NICKNAME, nicknameText);
		cv.put(KEY_SCORE, totalScore);
		return database.insert(DATABASE_TABLE,null, cv);
	}

	/**
	 * Get all scores that are in the database.
	 * @return String with every score on a new line
	 */
	public String getData() {
		
		// get all data from the table with the following columns
		String[] columns = {KEY_ROWID, KEY_NICKNAME, KEY_SCORE};
		Cursor cursor = database.query(DATABASE_TABLE, columns, null, null, null, null, KEY_SCORE + " DESC");
		
		// Create a StringBuilder to save the result string. Every result calls 6 times .append(),
		// so the StringBuilder needs a capacity of the result count * 6
		StringBuilder result = new StringBuilder(cursor.getCount() * 6);

		int index = 1;
		int indexName = cursor.getColumnIndex(KEY_NICKNAME);
		int indexScore = cursor.getColumnIndex(KEY_SCORE);

		for(cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()){
			result.append(index++);
			result.append(". ");
			result.append(cursor.getString(indexName));
			result.append(" ");
			result.append(cursor.getString(indexScore));
			result.append("\n");
		}
		return result.toString();
	}
}
